'..........................Class side codes

Imports System.Data.OleDb
Imports System.IO

Namespace OfficeDirectory
    Namespace Connecting
        Public Class msAcontrol
            Public AccessCon As New OleDbConnection With {.ConnectionString = "Provider=Microsoft.Ace.Oledb.12.0;Data Source=j:\directory.accdb"}
            Public AccessCmd As New OleDbCommand
            Public adp As New OleDbDataAdapter
            Public ds As DataSet = New DataSet()
            Public dt As New DataTable
            Public AccessCmd1 As New OleDbCommand
            Public theQry As String
            Public Function Toconnect() As Boolean
                Try
                    AccessCon.Open()
                    '......................qry displays data on formload in datagridview1
                    adp = New OleDbDataAdapter("select * from tblcontact", AccessCon)

                    adp.Fill(ds)
                    '......................close connection to db
                    AccessCon.Close()
                    Return True
                Catch ex As Exception
                    MsgBox(ex.Message)
                    Return False
                End Try

            End Function
        End Class
    End Namespace
    '........................Refresh
    Namespace Refresh
        Public Class Renew
            Inherits Connecting.msAcontrol
            Public Function ToRefresh() As Boolean
                On Error Resume Next
                If Not AccessCon.State = ConnectionState.Open Then
                    'open connection
                    AccessCon.Open()
                End If

                adp = New OleDb.OleDbDataAdapter("Select PhoneNumber as [Phone Number]," & _
                                                      "FirstName as [First Name], LastName as [Last Name],EmailAddress as [Email Address], PhoneExtension as [Phone Extension], Department as [Department] " & _
                                                     " from tblcontact order by PhoneNumber", AccessCon)
                adp.Fill(dt)
                AccessCon.Close()
            End Function
        End Class
    End Namespace

    Namespace Find
        Public Class ToFind
            Inherits Connecting.msAcontrol
            Public Function Found() As Boolean

                ds.Tables.Add(dt)
                AccessCon.Open()
                adp = New OleDbDataAdapter("select * from tblcontact where firstname like '%" & Form1.txtSearch.Text & "%'", AccessCon)
                adp.Fill(dt)

                Form1.DataGridView1.DataSource = dt.DefaultView
                AccessCon.Close()

            End Function
        End Class
    End Namespace

    Namespace Search
        Public Class ToSearch
            Inherits Connecting.msAcontrol
            Public Function ToSrch() As Boolean
               
                ds.Tables.Add(dt)
                AccessCon.Open()
                adp = New OleDbDataAdapter("select * from tblcontact where firstname like '%" & Form1.txtSearchByFname.Text & "%'", AccessCon)
                adp.Fill(dt)

                Form1.DataGridView2.DataSource = dt.DefaultView
                AccessCon.Close()

            End Function
        End Class
    End Namespace

    Namespace Update
        Class ToEdit
            Inherits Connecting.msAcontrol
            Public Function ToUpdate() As Boolean
                If Not AccessCon.State = ConnectionState.Open Then
                    AccessCon.Open()
                End If
                AccessCmd.Connection = AccessCon
                '.................Query statement to update data
                Dim SQL As String
                SQL = "Update [tblcontact] Set [FirstName]='" & Form1.txtfname.Text & "',[LastName]='" & Form1.txtlname.Text & "',[EmailAddress]='" & Form1.txtemailaddress.Text & _
                                        "',[PhoneExtension]='" & Form1.txtphoneExt.Text & "',[Department]='" & Form1.txtdepartment.Text & "' where [PhoneNumber]=" & Form1.txtphonenumber.Text

                AccessCmd = New OleDbCommand(SQL, AccessCon)
                Try
                    AccessCmd.ExecuteNonQuery()
                    AccessCmd.Dispose()
                    AccessCon.Close()

                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Function
        End Class
    End Namespace

    Namespace AddRecord
        Public Class Save
            Inherits Connecting.msAcontrol
            Public Function ToSave() As Boolean
                On Error Resume Next
                '..................................................connection string from namespace connection
                If Not AccessCon.State = ConnectionState.Open Then
                    AccessCon.Open()
                End If
                AccessCmd.Connection = AccessCon
                '.........................................if statement for msgbox to fill up phonenumber(PrimaryKey)
                If Form1.txtphonenumber.Text = "" Then
                    MsgBox("Please fill-up MobileNo!", MsgBoxStyle.Exclamation, "PrimaryKey!")
                Else
                    '.....................................query to check for existing data & displays msgbox that its already exist
                    theQry = "Select * from tblcontact where PhoneNumber=@PhoneNumber"
                    AccessCmd1 = New OleDbCommand(theQry, AccessCon)
                    AccessCmd1.Parameters.AddWithValue("PhoneNumber", Form1.txtphonenumber.Text)

                    Using reader As OleDbDataReader = AccessCmd1.ExecuteReader()
                        If reader.HasRows Then
                            'User already Exist
                            MsgBox("Contact Already Exist!", MsgBoxStyle.Exclamation, "Verify")
                        Else
                            '......................................................messagebox ask to save yes or no
                            If 6 = MsgBox("Save Data?", MsgBoxStyle.YesNo, "Save") Then
                                '..............................................add data to table from textbox
                                AccessCmd.CommandText = "InSert INTO tblcontact(PhoneNumber,FirstName,LastName,EmailAddress,PhoneExtension,Department)" & _
                                          " VALUES(" & Form1.txtphonenumber.Text & ", '" & Form1.txtfname.Text & "','" & _
                                          Form1.txtlname.Text & "','" & Form1.txtemailaddress.Text & "','" & Form1.txtphoneExt.Text & "','" & _
                                          Form1.txtdepartment.Text & "')"

                                AccessCmd.ExecuteNonQuery()
                                '.................msgbox confirming data is saved
                                MsgBox("Data is Saved.", MsgBoxStyle.Information, "SAVED")
                            End If
                        End If
                    End Using
                    '..............................close connection
                    AccessCon.Close()
                End If
            End Function
        End Class
    End Namespace

    Namespace ReCycleBin
        Public Class Delete
            Inherits Connecting.msAcontrol
            Public Function ToDelete() As Boolean
                Try

                    '....................opens connection to database
                    AccessCmd.Connection = AccessCon
                    AccessCmd.CommandType = CommandType.Text

                    AccessCmd.CommandText = "Delete * from tblcontact where phonenumber=" & Form1.txtphonenumber.Text

                    'Using cn
                    AccessCon.Open()
                    AccessCmd.ExecuteNonQuery()

                    'End Using
                    AccessCon.Close()

                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Function
        End Class
    End Namespace

End Namespace


